package lib.data;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.google.gdata.data.calendar.CalendarEntry;

import lib.base.EgoSystem;

/**
 * Department Class
 * 
 * @author jonas
 */
public class Department extends Data
{
	private ArrayList<Employee> employees;
	private Customer customer;
	
	public Department(int id, Customer cust) throws Exception
	{
		super("department");
		addPrimaryKey("customerID", cust.getField("customerID").toString());
		addPrimaryKey("departmentID", Integer.toString(id));
		loadData();
		
		customer = cust;
		employees = new ArrayList<Employee>();
	}
	
	public Customer getCustomer()
	{
		return customer;
	}
	
	/**
	 * load employees of this department
	 * 
	 */
	public void loadEmployees() throws Exception
	{
		employees = new ArrayList<Employee>();
		
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		ResultSet rs = stm.executeQuery(
			"SELECT employeeID FROM `department_employee` WHERE " +
			"`customerID`="+customer.getField("customerID")+" AND " +
			"`departmentID`="+getField("departmentID")+";"
		);
		while (rs.next())
		{
			employees.add(customer.getEmployee(rs.getString(1)));
		}
	}
	
	public ArrayList<Employee> getEmployees()
	{
		// don't return a pointer to my own ArrayList
		return new ArrayList<Employee> (employees);
	}

	static public Department newDepartment(Customer cust) throws Exception
	{
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		// get next departmentID
		String custID = cust.getField("customerID").toString();
		String nextID = "0";
		ResultSet rs = stm.executeQuery(
			"SELECT departmentID FROM `department` WHERE `customerID`="+ custID +" ORDER BY `departmentID` DESC LIMIT 1;"
		);
		if (rs.next())
		{
			nextID = String.valueOf(rs.getInt(1) +1);
		}
		stm.execute(
			"INSERT INTO `department` ("
			+"`departmentID` ,"
			+"`customerID` ,"
			+"`name` ,"
			+"`holidayCal` ,"
			+"`workingDays`"
			+") "
			+"VALUES ("
			+	"'"+ nextID +"', '"+ custID +"', 'new department', '', '31'"
			+");"
		);
		stm.close();
		return new Department(
			Integer.parseInt(nextID),
			cust
		);
	}
	
	public void updateEmployeesRelations(ArrayList<Employee> emps) throws Exception
	{
		// check whether relations were changed?
		boolean changed = false;
		ArrayList<Employee> t_emps = new ArrayList<Employee> (employees);
		next:
		for (int i=0; i<emps.size(); i++)
		{
			// do we have already this emp?
			for (int ii=0; ii<employees.size(); ii++)
			{
				if (emps.get(i).getField("employeeID").toString().equals( employees.get(ii).getField("employeeID").toString() ))
				{
					/* check if the role has been changed */
					if (emps.get(i).getField("role").toString().equals( employees.get(ii).getRole(this) ))
					{
						t_emps.remove(employees.get(ii));
						continue next;
					}
				}
			}
			// not found
			changed = true;
			break;
		}
		if (!changed && t_emps.size()==0)
		{
			// no changes
			return;
		}

		// delete old relations
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		stm.execute(
			"DELETE FROM `department_employee` WHERE customerID="+getField("customerID").toString()
			+ " AND departmentID="+getField("departmentID").toString()
		);
		
		// create new relations
		for (int i=0; i<emps.size(); i++)
		{
			stm.execute(
				"INSERT INTO `department_employee` ("
				+"`departmentID` ,"
				+"`customerID` ,"
				+"`employeeID` ,"
				+"`role`"
				+") "
				+"VALUES ("
				+""+ getField("departmentID") +", "+ getField("customerID") +", "+ emps.get(i).getField("employeeID") +", '"+ emps.get(i).getField("role") +"'"
				+");"
			);
		}
		stm.close();
	}
	
	public void delete() throws Exception
	{
		// first delete all relations
		Statement stm = EgoSystem.dbConnection.createStatement();
		String sql = "DELETE FROM `department_employee` WHERE "
			+ "customerID="+customer.getField("customerID").toString()
			+ " AND departmentID="+getField("departmentID").toString();
		stm.execute(sql);
		stm.close();
		
		// finally delete my entry
		super.delete();
		
		// delete calendar
		CalendarEntry cal = customer.holidayManager.getCalendar(this);
		if (cal != null)
		{
			cal.delete();
		}
	}
	
	/**
	 * add an employee to this department
	 * 
	 * @param emp Employee
	 * @param role String member / supervisor
	 * @throws Exception
	 */
	public void addEmployee(Employee emp, String role) throws Exception
	{
		emp.setField("role", role);
		employees.add(emp);
		updateEmployeesRelations(employees);
	}
}
